IF OBJECT_ID('dbo.RptCSCRPISatisfactionSurvey') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptCSCRPISatisfactionSurvey
    IF OBJECT_ID('dbo.RptCSCRPISatisfactionSurvey') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptCSCRPISatisfactionSurvey >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptCSCRPISatisfactionSurvey >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptCSCRPISatisfactionSurvey
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/1/2007
--
-- Description: Source of Report, "CSCRPISatisfactionSurvey"
--                  In Insight DB Reporting Services
--
-- Generates a list of agreements executed by CSCR in a given time period. 
-- CSCR uses this list (a) to generate the list of PIs to whom to send 
-- quarterly PI satisfaction surveys and (b) for selection of agreements 
-- to be included in our monthly QA review.
--
-- Parameters:  
--             @StartDate          Date of Status "CSR Executed"
--             @EndDate            
--             @CSCRCaseManager         
--             @Institution     
--             @Department
--             @Unit     
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptCSCRPISatisfactionSurvey()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/1/07     KC        Created
-- 10/11/07    KC        Added check for NULL AI.CSCRCaseManager
-- 10/15/07    KC        Added ignore of SubActivityType='CDA: Confidendiality Agreement (C)'
-- 10/18/07    KC        Fixed Searching on Dates with TimeComponent
-- 02/27/08    KC        Removed Security per Partners Request
-- 04/18/08    KC        Bug with Prop_Stat as PropStat fixed, made to
--                       work like Executed Agreements Report
-- 05/23/08    KC        Added AI.UnitId and AI.DepartmentId to ResultSet
--                       For Troubleshooting.
-- 06/18/08    KC        Increased size of @SubActivityType
-- 06/23/08    KC        Changed @SponsorType to Id, removed default of
--                       'INDUSTRY'
--
-- =======================================================================
CREATE PROCEDURE dbo.RptCSCRPISatisfactionSurvey
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @Institution                       VARCHAR(100) = NULL,             
     @Department                        VARCHAR(100) = NULL,             
     @Unit                              VARCHAR(100) = NULL,             
     @SponsorType                       VARCHAR(100) = NULL,
     @SubActivityType                   VARCHAR(5000) = NULL,
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     SET ANSI_WARNINGS ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT,
               @ConversionDate1 DATETIME,
               @ConversionDate2 DATETIME

     -- Default this, Records with these PropStatDate(s) will not be returned
     SET  @ConversionDate1 = '8/3/2007'
     SET  @ConversionDate2 = '8/4/2007'
               
     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @SponsorType IN ('ALL','','0', NULL) SET @SponsorType = NULL
     IF @SubActivityType IN ('ALL','','0') SET @SubActivityType = NULL
     
     -- Result Set
     SELECT    AI.PrincipalInvestigator AS PrincipalInvestigator,
               AI.FolderNumber AS FolderNumber,
               AI.SponsorName AS PrimarySponsor,
               AI.SponsorTypeName AS SponsorType,
               AI.CSCRCaseManager AS CSCRCaseManager,
               ISNULL(AI.CSCRCaseManagerName,'CSCR Case Manager Not Assigned') AS CSCRCaseManagerName,
               AI.PreAwardManager AS PreAwardManager,
               ISNULL(AI.PreAwardManagerName,'Pre Award Manager Not Assigned') AS PreAwardManagerName,
               ISNULL(IEP.ExecutedDate,RH.FEAPDate) AS CSCRExecutedDate,
               AI.ProposalTitle AS ProposalTitle, 
               AI.ActivityDescription AS ActivityType,
               UDF.SubActivityType AS SubActivityType,
               AI.InstrumentType AS SubAgreementType,         
               'CSCR - Executed' AS PropStat,
               IEP.ExecutedDate AS PropStatDate,
               ISNULL(RH.FEAPDate,IEP.ExecutedDate) AS FullyExecutedAwardedDate,
               AI.AgreementType AS AgreementType,
               AI.InstrumentType AS ProposalType,
               AI.Institution AS Institution,
               ISNULL(AI.Department,'(None)') AS Department,
               AI.ChiefCode AS ChiefCode,
               ISNULL(AI.Unit,'(None)') AS Unit,
               AI.ProposalStatus AS ProposalStatus,
               AI.InfoEdPropNumber AS InfoEdPropNumber,
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,
               AI.SponsorId AS SponsorId,
               AI.ResourceId AS AgrResourceId,
               AI.AgreementId AS AgreementId,
               AI.DepartmentId,
               AI.UnitId,
               AI.SponsorTypeId
     FROM      dbo.AgrAgreement_Info AI LEFT OUTER JOIN

               -- Searching on FIRST Status Change Date!
               -- Master & Amendment Proposals: Date below...
               dbo.vwInfoEdMasterAmendments IEP ON AI.InfoEdPropNumber = IEP.prop_no LEFT OUTER JOIN
               
               dbo.InfoEdUDFProposal UDF ON AI.InfoEdPropNumber = UDF.PropNumber LEFT OUTER JOIN
               dbo.RexStatusHistory RH ON AI.FolderNumber = RH.FolderNumber
               
     WHERE     (((dbo.fnRemoveTime(IEP.ExecutedDate) BETWEEN @StartDate AND @EndDate 
               AND dbo.fnRemoveTime(IEP.ExecutedDate) NOT IN (@ConversionDate1,@ConversionDate2)) OR
               dbo.fnRemoveTime(RH.FEAPDate) BETWEEN @StartDate AND @EndDate )) AND
               (RTRIM(AI.ActivityDescription) = 'Clinical Research') AND
               (RTRIM(AI.SubActivityType) != 'CDA: Confidendiality Agreement (C)') AND
               (AI.CSCRCaseManager IS NOT NULL AND RTRIM(AI.CSCRCaseManager) != '') AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @Institution ))) AND
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @Department ))) AND
               (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @Unit ))) AND
               (@SponsorType IS NULL OR AI.SponsorTypeId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @SponsorType ))) AND
               (@SubActivityType IS NULL OR UDF.SubActivityType IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @SubActivityType )))
     ORDER BY AI.PrincipalInvestigator, AI.Institution, AI.Department, AI.Unit


     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1
          
     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptCSCRPISatisfactionSurvey') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptCSCRPISatisfactionSurvey >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptCSCRPISatisfactionSurvey >>>'
go

GRANT EXECUTE ON dbo.RptCSCRPISatisfactionSurvey TO InsightReport,Insight
go

